package com.credit.shtl.dao.student;/**
 * @Classname StudentUserDAO
 * @Description 学生信息DAO接口
 * @Date 2020/10/5 18:43
 * @Created by SHTL
 */


import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;

/**
 * @InterfaceName StudentUserDAO
 * @Description TODO
 * @Author SHTL
 * @Date 2020/10/5 18:43
 * @Version 1.0
 */
public interface StudentUserDAO extends JpaRepository<StudentUser, Integer>,
        JpaSpecificationExecutor<StudentUser> {


    // 根据学号和邮箱获取学生信息
    StudentUser findByXuehaoAndEmail(Integer xuehao, String email);

    // 根据学号、邮箱、密保进行查找密码
    StudentUser findByXuehaoAndEmailAndMibao(Integer xuehao, String email, String mibao);

    StudentUser findByXuehao(Integer xuehao);

    // 根据学号删除学生信息
    // 根据账户信息删除指定的教师账号
    @Modifying
    @Transactional
    @Query(value = "delete from student_table as a where a.xuehao=:xuehao", nativeQuery = true)
    Integer deleteByXuehao(@Param("xuehao") Integer xuehao);

    StudentUser getByXuehaoAndPassword(Integer xuehao, String password);

    // 学生管理页面   获取学院名称
    @Modifying
    @Query(value = "select college from student_table GROUP BY college", nativeQuery = true)
    List<Object> getByCollegegroup();

    // 学生管理页面    获取专业名称
    @Modifying
    @Query(value = "select major from student_table GROUP BY major", nativeQuery = true)
    List<Object> getByMajorgroup();


    // 学生管理页面    获取班级名称
    @Modifying
    @Query(value = "select banji from student_table GROUP BY banji", nativeQuery = true)
    List<Object> getByBanjigroup();

    // 根据班级字段获取该班级所有学号信息
    @Modifying
    @Query(value = "select a.xuehao from student_table as a where a.banji=:banji", nativeQuery = true)
    Integer[] findByXhinBJ(@Param("banji") String banji);


    // 查询当前课程所有选课学生信息（参数只有课程id）
    @Query(value = "select b.ctime,a.name,a.xuehao,a.sex,a.college,a.major,a.banji from selection_table as b,student_table as a where b.kechengid=:kechengid and a.xuehao=b.xuehao", nativeQuery = true)
    List<Map<String, Object>> getAllSelection(@Param("kechengid") Integer kechengid);


    /**
     * 获取当前学生账号的所有选课信息
     **/
    // 1. 没有筛选条件
    @Query(value = "select  a.ctime,a.id,b.kechengid,b.kechengname,b.kechengleibie,c.`name`,b.xuefen from selection_table as a,curriculum_table as b,teacher_table as c where a.kechengid=b.kechengid and b.kechengteacher=c.zhanghu and a.xuehao=:xuehao order by a.kechengid", nativeQuery = true)
    List<Map<String, Object>> SelecttionNo(@Param("xuehao") Integer xuehao);

    // 2. 只有筛选条件为 课程类型
    @Query(value = "select  a.ctime,a.id,b.kechengid,b.kechengname,b.kechengleibie,c.`name`,b.xuefen from selection_table as a,curriculum_table as b,teacher_table as c where a.kechengid=b.kechengid and b.kechengteacher=c.zhanghu and a.xuehao=:xuehao and b.kechengleibie=:kechengleibie order by a.kechengid", nativeQuery = true)
    List<Map<String, Object>> SelectionLeixing(@Param("xuehao") Integer xuehao, @Param("kechengleibie") String kechengleibie);

    // 3. 只有筛选条件为 模糊查询
    @Query(value = "select  a.ctime,a.id,b.kechengid,b.kechengname,b.kechengleibie,c.`name`,b.xuefen from selection_table as a,curriculum_table as b,teacher_table as c where a.kechengid=b.kechengid and b.kechengteacher=c.zhanghu and a.xuehao=:xuehao and (b.kechengid like concat('%',:like,'%') or b.kechengname like concat('%',:like,'%'))  order by a.kechengid", nativeQuery = true)
    List<Map<String, Object>> SelectionLike(@Param("xuehao") Integer xuehao, @Param("like") String like);

    // 4. 只有筛选条件为 学分
    @Query(value = "select  a.ctime,a.id,b.kechengid,b.kechengname,b.kechengleibie,c.`name`,b.xuefen from selection_table as a,curriculum_table as b,teacher_table as c where a.kechengid=b.kechengid and b.kechengteacher=c.zhanghu and a.xuehao=:xuehao and b.xuefen=:xuefen  order by a.kechengid", nativeQuery = true)
    List<Map<String, Object>> SelectionXuefen(@Param("xuehao") Integer xuehao, @Param("xuefen") Integer xuefen);

    // 5. 筛选条件为 学分 和 模糊查询
    @Query(value = "select  a.ctime,a.id,b.kechengid,b.kechengname,b.kechengleibie,c.`name`,b.xuefen from selection_table as a,curriculum_table as b,teacher_table as c where a.kechengid=b.kechengid and b.kechengteacher=c.zhanghu and a.xuehao=:xuehao and b.xuefen=:xuefen and (b.kechengid like concat('%',:like,'%') or b.kechengname like concat('%',:like,'%')) order by a.kechengid", nativeQuery = true)
    List<Map<String, Object>> SelectionXandLike(@Param("xuehao") Integer xuehao, @Param("xuefen") Integer xuefen, @Param("like") String like);

    // 6. 筛选条件为 学分和课程类型
    @Query(value = "select  a.ctime,a.id,b.kechengid,b.kechengname,b.kechengleibie,c.`name`,b.xuefen from selection_table as a,curriculum_table as b,teacher_table as c where a.kechengid=b.kechengid and b.kechengteacher=c.zhanghu and a.xuehao=:xuehao and b.xuefen=:xuefen and b.kechengleibie=:kechengleibie order by a.kechengid", nativeQuery = true)
    List<Map<String, Object>> SelectionXandLeibie(@Param("xuehao") Integer xuehao, @Param("xuefen") Integer xuefen, @Param("kechengleibie") String kechengleibie);

    // 7. 筛选条件为 模糊查询 和 课程类型
    @Query(value = "select  a.ctime,a.id,b.kechengid,b.kechengname,b.kechengleibie,c.`name`,b.xuefen from selection_table as a,curriculum_table as b,teacher_table as c where a.kechengid=b.kechengid and b.kechengteacher=c.zhanghu and a.xuehao=:xuehao  and (b.kechengid like concat('%',:like,'%') or b.kechengname like concat('%',:like,'%')) and b.kechengleibie=:kechengleibie order by a.kechengid", nativeQuery = true)
    List<Map<String, Object>> SelectionLandLiebie(@Param("xuehao") Integer xuehao, @Param("like") String like, @Param("kechengleibie") String kehcengleibie);

    // 8. 筛选条件为 模糊查询 和 学分 以及 课程类型
    @Query(value = "select  a.ctime,a.id,b.kechengid,b.kechengname,b.kechengleibie,c.`name`,b.xuefen from selection_table as a,curriculum_table as b,teacher_table as c where a.kechengid=b.kechengid and b.kechengteacher=c.zhanghu and a.xuehao=:xuehao and (b.kechengid like concat('%',:like,'%') or b.kechengname like concat('%',:like,'%')) and b.kechengleibie=:kechengleibie and b.xuefen=:xuefen and b.kechengleibie=:kechengleibie order by a.kechengid", nativeQuery = true)
    List<Map<String, Object>> SelectionAll(@Param("xuehao") Integer xuehao, @Param("like") String like, @Param("xuefen") Integer xuefen, @Param("kechengleibie") String kechengleibie);
}
